package edu.zju.gis.dbfg.server.controller;

import edu.zju.gis.dbfg.common.Page;
import edu.zju.gis.dbfg.common.Result;
import edu.zju.gis.dbfg.server.model.*;
import edu.zju.gis.dbfg.server.model.vo.FeatureRecord;
import edu.zju.gis.dbfg.server.model.vo.tableRecordsWithTotalsize;
import edu.zju.gis.dbfg.server.service.ApiSourceService;
import edu.zju.gis.dbfg.server.service.ExternalDataSourceService;
import edu.zju.gis.dbfg.server.service.MysqlSourceService;
import edu.zju.gis.dbfg.server.service.PgSourceService;
import edu.zju.gis.dbfg.server.util.*;
import org.json.JSONArray;
import org.json.simple.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

@CrossOrigin
@Controller
@RequestMapping("/external2")
public class ExternalDataController2 {
    @Autowired
    private PgSourceService pgSourceService;
    @Autowired
    private MysqlSourceService mysqlSourceService;
    @Autowired
    private ApiSourceService apiSourceService;
    @Autowired
    private ExternalDataSourceService externalDataSourceService;
//    private List<JSONObject> pgSchemas;

    @RequestMapping(value = "/pg/all", method = RequestMethod.POST)
    @ResponseBody
    public Result getAll() {
        Result<List<PgSource>> result = new Result<>();
        return result.setBody(pgSourceService.search()).setCode(200);
    }

    @RequestMapping(value = "/connect", method = RequestMethod.POST)
    @ResponseBody
    public Result connect(@RequestBody ExternalDataArgs sourceConnect) {
        Result<String> result = new Result<>();
        try {
            ExternalHelper externalHelper;
            switch (sourceConnect.getType()) {
                case "postgresql":
                    externalHelper = new PgHelper();
                    break;
                case "mysql":
                    externalHelper = new MysqlHelper();
                    break;
                case "api":
                    externalHelper = new ApiHelper();
                    break;
                default:
                    externalHelper = null;
            }
            JSONObject jsonObject = externalHelper.testConnection(sourceConnect);
            result.setBody("连接外部数据成功").setCode(200).setMessage("连接外部数据成功");
            if (jsonObject != null && jsonObject.containsKey("errcode")) {
                result.setBody("连接外部数据失败").setCode(201).setMessage("连接外部数据失败");
            }
            externalHelper.close();
        } catch (Exception e) {
            result.setBody("连接外部数据失败").setCode(201).setMessage(e.getMessage());
        }
        return result;
    }

    @RequestMapping(value = "/pg/tables", method = RequestMethod.POST)
    @ResponseBody
    public Result tablesGet(@RequestBody ExternalDataArgs sourceConnect) {
        Result<List<JSONObject>> result = new Result<>();
        switch (sourceConnect.getType()) {
            case "postgresql":
                Connection c = null;
                try {
                    Class.forName("org.postgresql.Driver");
                    String url = "jdbc:postgresql://" + sourceConnect.getIp() + ":" + sourceConnect.getPort() + "/" + sourceConnect.getDatabaseName();
                    c = DriverManager.getConnection(url,
                            sourceConnect.getUserName(), sourceConnect.getPassword());
//                    c = DriverManager.getConnection("jdbc:postgresql://101.132.135.1:5432/postgres", "postgres", "postgres");
                    Statement stmt = c.createStatement();
                    String selectSql = "select * from pg_tables where schemaname ='" + sourceConnect.getSchemaName() + "'";
//                    ResultSet rs = stmt.executeQuery("select * from pg_tables where schemaname = 'public'");
                    ResultSet rs = stmt.executeQuery(selectSql);
                    List<JSONObject> tables = new ArrayList<>();
                    while (rs.next()) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("value", rs.getString("tablename"));
                        jsonObject.put("label", rs.getString("tablename"));
                        tables.add(jsonObject);
                        String name = rs.getString("tablename");
                        System.out.println("表的名字是" + name);
                    }
                    result.setBody(tables).setCode(200);
                    rs.close();
                    stmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                    System.err.println(e.getClass().getName() + ": " + e.getMessage());
                    result.setMessage("获取postgresql数据表失败").setCode(201);
                }
                break;
            default:
                result.setMessage("没有匹配到相应类型数据源").setCode(201);
                break;
        }
        return result;

    }


    @RequestMapping(value = "/pg/schemas", method = RequestMethod.POST)
    @ResponseBody
    public Result tableScheGet(@RequestBody ExternalDataArgs sourceConnect) {
        Result<List<JSONObject>> result = new Result<>();
        switch (sourceConnect.getType()) {
            case "postgresql":
                Connection c = null;
                try {
                    Class.forName("org.postgresql.Driver");
                    String url = "jdbc:postgresql://" + sourceConnect.getIp() + ":" + sourceConnect.getPort() + "/" + sourceConnect.getDatabaseName();
                    c = DriverManager.getConnection(url,
                            sourceConnect.getUserName(), sourceConnect.getPassword());
//                    c = DriverManager.getConnection("jdbc:postgresql://101.132.135.1:5432/postgres", "postgres", "postgres");
                    Statement stmt = c.createStatement();
                    String selectScheSql = "select * from information_schema.schemata";
//                    ResultSet rs = stmt.executeQuery("select * from pg_tables where schemaname = 'public'");
                    ResultSet rs = stmt.executeQuery(selectScheSql);
                    List<JSONObject> tables = new ArrayList<>();
                    while (rs.next()) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("value", rs.getString("schema_name"));
                        jsonObject.put("label", rs.getString("schema_name"));
                        tables.add(jsonObject);
                        String name = rs.getString("schema_name");
                    }
                    result.setBody(tables).setCode(200);
                    rs.close();
                    stmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                    System.err.println(e.getClass().getName() + ": " + e.getMessage());
                    result.setMessage("获取postgresql数据库模式失败").setCode(201);
                }
                break;
            default:
                result.setMessage("没有匹配到相应类型数据源").setCode(201);
                break;
        }
        return result;
    }

    @RequestMapping(value = "/query", method = RequestMethod.POST)
    @ResponseBody
    public Result dataGet(@RequestBody ExternalDataArgs sourceConnect) {
        Result<List<JSONObject>> result = new Result<>();
        ExternalHelper externalHelper;
        try {
            switch (sourceConnect.getType()) {
                case "postgresql":
                    externalHelper = new PgHelper();
                    break;
                case "mysql":
                    externalHelper = new MysqlHelper();
                    break;
                default:
                    externalHelper = null;
            }
            result.setBody(externalHelper.queryConstruct(sourceConnect)).setCode(200).setMessage("获取数据成功");
            externalHelper.close();
        } catch (Exception e) {
            result.setCode(201).setMessage("获取数据失败");
        }
        return result;
    }

    //获得用户个人所有的外部链接数据
    @RequestMapping(value = "/all", method = RequestMethod.GET)
    @ResponseBody
    public Result externalAll(@SessionAttribute("userId") String userId, Page page) {
        Page<ExternalDataSource> result = externalDataSourceService.search(userId, page);
        return Result.success().setBody(result);
    }

    @RequestMapping(value = "/add", method = RequestMethod.POST)
    @ResponseBody
    public Result externalInsert(@SessionAttribute("userId") String userId, @RequestBody ExternalDataArgs sourceConnect) {
        String uuid = UUID.randomUUID().toString();
        int result1 = 0;
        int result2 = 0;
        ExternalDataSource externalDataSource = new ExternalDataSource(sourceConnect);
        externalDataSource.setId(uuid);
        externalDataSource.setUserid(userId);
        result1 = externalDataSourceService.insert(externalDataSource);
        switch (sourceConnect.getType()) {
            case "postgresql":
                PgSource pgSource = new PgSource(sourceConnect);
                pgSource.setId(uuid);
                result2 = pgSourceService.insert(pgSource);
                break;
            case "mysql":
                MysqlSource mysqlSource = new MysqlSource(sourceConnect);
                mysqlSource.setId(uuid);
                result2 = mysqlSourceService.insert(mysqlSource);
                break;
            case "api":
                ApiSource apiSource = new ApiSource();
                apiSource.setId(uuid);
                apiSource.setConnectionName(sourceConnect.getUrl().substring(0, sourceConnect.getUrl().indexOf("?") - 1));
                apiSource.setParams(sourceConnect.getUrl().substring(sourceConnect.getUrl().indexOf("?"), sourceConnect.getUrl().length()));
                result2 = apiSourceService.insert(apiSource);
                break;
            default:
        }
        if (result1 == 1 && result2 == 1) {
            return Result.success().setMessage("添加外部数据源成功");
        } else {
            return Result.error().setMessage("添加外部数据源失败");
        }
    }

    //获取某个表中的全部记录
    @RequestMapping(value = "/tablePreview/{id}", method = RequestMethod.POST)
    @ResponseBody
    public Result tablesGet(@PathVariable String id, int pageNo, int pageSize, String type) {
        ExternalDataArgs sourceConnect = new ExternalDataArgs();
        ExternalDataSource externalDataSource = externalDataSourceService.searchById(id);
        Result<tableRecordsWithTotalsize> result = new Result<>();
        try {
            ExternalHelper externalHelper;
            switch (type) {
                case "postgresql":
                    PgSource pgSource = pgSourceService.searchById(id);
                    sourceConnect.setType(externalDataSource.getType());
                    sourceConnect.fromPgsource(pgSource);
//                    sourceConnect.setDatabaseName(pgSource.getDatabase());
//                    sourceConnect.setDataName(pgSource.getDataName());
//                    sourceConnect.setIp(pgSource.getIP());
//                    sourceConnect.setPort(pgSource.getPort());
//                    sourceConnect.setPassword(pgSource.getPassword());
//                    sourceConnect.setUserName(pgSource.getUserName());
//                    sourceConnect.setTableName(pgSource.getTableName());
//                    sourceConnect.setSchemaName(pgSource.getSchemaName());
//                    sourceConnect.setSpatialField(pgSource.getSpatialField());
                    externalHelper = new PgHelper();
                    break;
                case "mysql":
                    MysqlSource mysqlSource = mysqlSourceService.searchById(id);
                    sourceConnect.setType(externalDataSource.getType());
                    sourceConnect.fromMysqlsource(mysqlSource);
//                    sourceConnect.setDatabaseName(mysqlSource.getDatabase());
//                    sourceConnect.setDataName(mysqlSource.getDataName());
//                    sourceConnect.setIp(mysqlSource.getIP());
//                    sourceConnect.setPort(mysqlSource.getPort());
//                    sourceConnect.setPassword(mysqlSource.getPassword());
//                    sourceConnect.setUserName(mysqlSource.getUserName());
//                    sourceConnect.setTableName(mysqlSource.getTableName());
//                    sourceConnect.setSpatialField(mysqlSource.getSpatialField());
                    externalHelper = new MysqlHelper();
                    break;
                case "api":
                    ApiSource apiSource=apiSourceService.searchById(id);
                    sourceConnect.setUrl(apiSource.getConnectionName()+"?"+apiSource.getParams());
                    externalHelper=new ApiHelper();
                    break;
                default:
                    externalHelper = null;
            }
            result.setCode(200).setBody(externalHelper.pageSearh(sourceConnect, pageNo, pageSize));
            externalHelper.close();
        } catch (Exception e) {
            System.out.println(e);
            result.setMessage("数据预览失败").setCode(201);
        }
        return result;
    }

    @RequestMapping(value = "/tableInfo/{id}", method = RequestMethod.POST)
    @ResponseBody
    public Result tableGet(@PathVariable String id, String type) {
        switch (type) {
            case "postgresql":
                PgSource pgSource = pgSourceService.searchById(id);
                return Result.success().setBody(pgSource);
            case "mysql":
                MysqlSource mysqlSource = mysqlSourceService.searchById(id);
                return Result.success().setBody(mysqlSource);
            case "api":
                ApiSource apiSource = apiSourceService.searchById(id);
                return Result.success().setBody(apiSource);
            default:
                return Result.error().setMessage("预览数据集失败");
        }
    }

    @RequestMapping(value = "/tableDelete/{id}", method = RequestMethod.POST)
    @ResponseBody
    public Result tablesDelete(@PathVariable String id, String type) {
        Result<String> result = new Result<>();
        int res1 = externalDataSourceService.deleteByPrimaryKey(id);
        int res2 = 0;
        switch (type) {
            case "postgresql":
                res2 = pgSourceService.deleteByPrimaryKey(id);
                break;
            case "mysql":
                res2 = mysqlSourceService.deleteByPrimaryKey(id);
                break;
            case "api":
                res2 = apiSourceService.deleteByPrimaryKey(id);
                break;
            default:
                res2 = 0;
        }
        if (res1 == 1 && res2 == 1) {
            result.setMessage("删除成功").setCode(200);
        } else {
            result.setMessage("删除失败").setCode(201);
        }
        return result;
    }

    //点选
    @RequestMapping(value = "/spatialQuery/identify/{id}", method = RequestMethod.POST)
    @ResponseBody
    public Result identifyQuery(@PathVariable String id, String type, String lon, String lat) {
        ExternalDataSource externalDataSource = externalDataSourceService.searchById(id);
        ExternalDataArgs sourceConnect = new ExternalDataArgs();
        ExternalHelper externalHelper;
        Result<FeatureRecord> result = new Result<>();
        try {
            switch (type) {
                case "postgresql":
                    PgSource pgSource = pgSourceService.searchById(id);
                    sourceConnect.setType(externalDataSource.getType());
                    sourceConnect.fromPgsource(pgSource);
//                    sourceConnect.setDatabaseName(pgSource.getDatabase());
//                    sourceConnect.setDataName(pgSource.getDataName());
//                    sourceConnect.setIp(pgSource.getIP());
//                    sourceConnect.setPort(pgSource.getPort());
//                    sourceConnect.setPassword(pgSource.getPassword());
//                    sourceConnect.setUserName(pgSource.getUserName());
//                    sourceConnect.setTableName(pgSource.getTableName());
//                    sourceConnect.setSchemaName(pgSource.getSchemaName());
//                    sourceConnect.setSpatialField(pgSource.getSpatialField());
                    externalHelper = new PgHelper();
                    break;
                case "mysql":
                    MysqlSource mysqlSource = mysqlSourceService.searchById(id);
                    sourceConnect.setType(externalDataSource.getType());
                    sourceConnect.fromMysqlsource(mysqlSource);
//                    sourceConnect.setDatabaseName(mysqlSource.getDatabase());
//                    sourceConnect.setDataName(mysqlSource.getDataName());
//                    sourceConnect.setIp(mysqlSource.getIP());
//                    sourceConnect.setPort(mysqlSource.getPort());
//                    sourceConnect.setPassword(mysqlSource.getPassword());
//                    sourceConnect.setUserName(mysqlSource.getUserName());
//                    sourceConnect.setTableName(mysqlSource.getTableName());
//                    sourceConnect.setSpatialField(mysqlSource.getSpatialField());
                    externalHelper = new MysqlHelper();
                    break;
                default:
                    externalHelper = null;
            }
            FeatureRecord resFea = externalHelper.identifyQuery(lon, lat, sourceConnect);
            result.setBody(resFea);
        } catch (Exception e) {
            System.out.println(e);
        }
        return result;
    }

    //框选
    @RequestMapping(value = "/spatialQuery/area/{id}", method = RequestMethod.POST)
    @ResponseBody
    public Result identifyQuery(@PathVariable String id, @RequestBody String requestBody, String type) {
        JSONArray req = new JSONArray(requestBody);
        ExternalDataSource externalDataSource = externalDataSourceService.searchById(id);
        ExternalDataArgs sourceConnect = new ExternalDataArgs();
        ExternalHelper externalHelper;
        Result<List<FeatureRecord>> result = new Result<>();
        try {
            switch (type) {
                case "postgresql":
                    PgSource pgSource = pgSourceService.searchById(id);
                    sourceConnect.setType(externalDataSource.getType());
                    sourceConnect.fromPgsource(pgSource);
//                    sourceConnect.setDatabaseName(pgSource.getDatabase());
//                    sourceConnect.setDataName(pgSource.getDataName());
//                    sourceConnect.setIp(pgSource.getIP());
//                    sourceConnect.setPort(pgSource.getPort());
//                    sourceConnect.setPassword(pgSource.getPassword());
//                    sourceConnect.setUserName(pgSource.getUserName());
//                    sourceConnect.setTableName(pgSource.getTableName());
//                    sourceConnect.setSchemaName(pgSource.getSchemaName());
//                    sourceConnect.setSpatialField(pgSource.getSpatialField());
                    externalHelper = new PgHelper();
                    break;
                case "mysql":
                    MysqlSource mysqlSource = mysqlSourceService.searchById(id);
                    sourceConnect.setType(externalDataSource.getType());
                    sourceConnect.fromMysqlsource(mysqlSource);
//                    sourceConnect.setDatabaseName(mysqlSource.getDatabase());
//                    sourceConnect.setDataName(mysqlSource.getDataName());
//                    sourceConnect.setIp(mysqlSource.getIP());
//                    sourceConnect.setPort(mysqlSource.getPort());
//                    sourceConnect.setPassword(mysqlSource.getPassword());
//                    sourceConnect.setUserName(mysqlSource.getUserName());
//                    sourceConnect.setTableName(mysqlSource.getTableName());
//                    sourceConnect.setSpatialField(mysqlSource.getSpatialField());
                    externalHelper = new MysqlHelper();
                    break;
                default:
                    externalHelper = null;
            }
            List<FeatureRecord> resFea = externalHelper.areaQuery(req, sourceConnect);
            result.setBody(resFea);
        } catch (Exception e) {
            System.out.println(e);
        }
        return result;
    }

}
